package com.test.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.management.RuntimeErrorException;

public class SQLiteUtils {
	private static String fileName;
	static {
		Properties prop = new Properties();
		try {
			prop.load(new FileInputStream(SQLiteUtils.class.getResource("").getFile()+ "db.properties"));
			fileName = prop.getProperty("db.file");
			String parent = SQLiteUtils.class.getResource("/").getFile();
			parent = parent.substring(0, parent.indexOf("WEB-INF/"));
			fileName = parent + fileName;
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
	}
	
	
	/*
	 * 初始化数据库,只执行一次,文件存在就不执行了
	 * 创建表
	 */
	public static void init() {
		if (fileName == null) {
			throw new RuntimeErrorException(new Error("文件名不存在"));
		}
		File file = new File(fileName);
		if (!file.exists()) {
			System.out.println("创建文件.....");
			if (!file.getParentFile().exists()) {
				file.getParentFile().mkdirs();
			}
			Connection conn = null;
			try {
				file.createNewFile();
				
				Class.forName(org.sqlite.JDBC.class.getName());
				conn = DriverManager.getConnection("jdbc:sqlite:"
						+ file.getCanonicalPath());
				Statement stat = conn.createStatement();
				conn.setAutoCommit(false);
				
				stat.execute("create table t_category(id Integer primary key autoincrement, name varchar(20) not null, order_num integer, pid integer)");//分类
				stat.execute("create table t_article(id Integer primary key autoincrement, category_id integer, title varchar(255) not null,content varchar(4000), create_time TIMESTAMP, mod_time TIMESTAMP)");
				
				conn.commit();
				conn.close(); // 结束数据库的连接
			} catch (Exception e) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 获取连接
	 */
	public static Connection getConnection()  {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return null;
		}
		
		Connection conn = null;
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + fileName);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * execute example
	 */
	public static void execute(String sql) {
		Connection conn = getConnection();
		try {
			Statement stat = conn.createStatement();
			conn.setAutoCommit(false);
			stat.execute(sql);
			
			conn.commit();
			conn.close(); // 结束数据库的连接
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException(e1);
			}
			throw new RuntimeException(e);
		}
	}
	
	
	public static void main(String[] args) {
		System.out.println(fileName);
		init();
		System.out.println(getConnection());
		
		//SELECT last_insert_rowid())+1
	}
}
